-- This script was generated by the Schema Diff utility in pgAdmin 4
-- For the circular dependencies, the order in which Schema Diff writes the objects is not very sophisticated
-- and may require manual changes to the script to ensure changes are applied in the correct order.
-- Please report an issue for any failure with the reproduction steps.

DROP POLICY "Does user have access to response using RBAC" on response;
DROP POLICY "Select using auth token" on request;
CREATE TABLE IF NOT EXISTS public.user_api_keys
(
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    api_key_hash text COLLATE pg_catalog."default" NOT NULL,
    api_key_preview text COLLATE pg_catalog."default" NOT NULL,
    user_id uuid NOT NULL,
    CONSTRAINT user_api_keys_pkey PRIMARY KEY (api_key_hash)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.user_api_keys
    OWNER to postgres;

ALTER TABLE IF EXISTS public.user_api_keys
    ENABLE ROW LEVEL SECURITY;

GRANT ALL ON TABLE public.user_api_keys TO anon;

GRANT ALL ON TABLE public.user_api_keys TO authenticated;

GRANT ALL ON TABLE public.user_api_keys TO postgres;

GRANT ALL ON TABLE public.user_api_keys TO service_role;

CREATE OR REPLACE FUNCTION public.check_response_access(
	this_associated_request_id uuid,
	this_user_id uuid)
    RETURNS boolean
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
   SELECT EXISTS (select * from user_api_keys uak left join request r on r.auth_hash = uak.api_key_hash where r.id = this_associated_request_id and uak.user_id = this_user_id)
$BODY$;

ALTER FUNCTION public.check_response_access(uuid, uuid)
    OWNER TO postgres;

GRANT EXECUTE ON FUNCTION public.check_response_access(uuid, uuid) TO PUBLIC;

GRANT EXECUTE ON FUNCTION public.check_response_access(uuid, uuid) TO anon;

GRANT EXECUTE ON FUNCTION public.check_response_access(uuid, uuid) TO authenticated;

GRANT EXECUTE ON FUNCTION public.check_response_access(uuid, uuid) TO postgres;

GRANT EXECUTE ON FUNCTION public.check_response_access(uuid, uuid) TO service_role;

CREATE OR REPLACE FUNCTION public.check_request_access(
	this_auth_hash text,
	this_user_id uuid)
    RETURNS boolean
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
   SELECT EXISTS (select * from user_api_keys uak where uak.api_key_hash = this_auth_hash and uak.user_id = this_user_id)
$BODY$;

ALTER FUNCTION public.check_request_access(text, uuid)
    OWNER TO postgres;

GRANT EXECUTE ON FUNCTION public.check_request_access(text, uuid) TO PUBLIC;

GRANT EXECUTE ON FUNCTION public.check_request_access(text, uuid) TO anon;

GRANT EXECUTE ON FUNCTION public.check_request_access(text, uuid) TO authenticated;

GRANT EXECUTE ON FUNCTION public.check_request_access(text, uuid) TO postgres;

GRANT EXECUTE ON FUNCTION public.check_request_access(text, uuid) TO service_role;

DROP FUNCTION IF EXISTS public.response_has_access(this_accociated_request_id uuid, this_auth_hash text);


CREATE POLICY "Enable delete access for auth users"
    ON public.user_api_keys
    AS PERMISSIVE
    FOR DELETE
    TO public
    USING (((auth.uid() = user_id)));
CREATE POLICY "Enable inserts for users based on user_id"
    ON public.user_api_keys
    AS PERMISSIVE
    FOR INSERT
    TO public
    WITH CHECK (((auth.uid() = user_id)));
CREATE POLICY "Enable read access for users"
    ON public.user_api_keys
    AS PERMISSIVE
    FOR SELECT
    TO public
    USING ((auth.uid() = user_id));

CREATE POLICY "User RBAC with user_api_keys table"
    ON public.request
    AS PERMISSIVE
    FOR SELECT
    TO public
    USING (check_request_access(auth_hash, auth.uid()));

DROP POLICY IF EXISTS "Select using auth token" ON public.request;

CREATE POLICY "User RBAC with user_api_keys table and request table"
    ON public.response
    AS PERMISSIVE
    FOR SELECT
    TO public
    USING (check_response_access(request, auth.uid()));

DROP POLICY IF EXISTS "Does user have access to response using RBAC" ON public.response;

CREATE OR REPLACE VIEW public.response_and_request
    AS
     SELECT response.body AS response_body,
    response.id AS response_id,
    response.created_at AS response_created_at,
    request.id AS request_id,
    request.body AS request_body,
    request.path AS request_path,
    request.created_at AS request_created_at,
    request.user_id AS request_user_id,
    user_api_keys.api_key_preview
   FROM response
     LEFT JOIN request ON request.id = response.request
     LEFT JOIN user_api_keys ON user_api_keys.api_key_hash = request.auth_hash;
